package org.light.exceltrnslator;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.light.domain.Domain;
import org.light.domain.Field;
import org.light.domain.ManyToManyCandidate;
import org.light.domain.Project;
import org.light.domain.ValidateInfo;
import org.light.exception.ValidateException;
import org.light.utils.DomainUtil;
import org.light.utils.StringUtil;

public class MtmCandidateExcelTranslator {
	public ManyToManyCandidate translate(Project project,HSSFSheet sheet,List<Domain> domains,List<List<Domain>> datadomains) throws Exception {
		return translateManyToManyCandidate(project,sheet,domains,datadomains);
	}
	
	public List<ManyToManyCandidate> translateValues(Project project,HSSFSheet sheet,List<Domain> domains,List<List<Domain>> datadomains) throws Exception {
		return translateManyToManyCandidateValues(project,sheet,domains,datadomains);
	}
	
	public ValidateInfo validate(ManyToManyCandidate mtmc) {
		ValidateInfo info = new ValidateInfo();
		return info;
	}
	
	public ManyToManyCandidate translateManyToManyCandidate(Project project,HSSFSheet sheet,List<Domain> domains,List<List<Domain>> datadomains)  throws Exception{
		String masterName = readMetaField(sheet, "masterDomain");
		String slaveName = readMetaField(sheet, "slaveDomain");
		String masterIdName = readMetaField(sheet, "masterIdName");
		String slaveIdName = readMetaField(sheet, "slaveIdName");
		String slaveAlias = readMetaField(sheet, "slaveAlias");
		String tableprefix = readMetaField(sheet, "tableprefix");
		String label = readMetaField(sheet, "label");
		
		ValidateInfo info = new ValidateInfo();
		ManyToManyCandidate mtmc = new ManyToManyCandidate();
		
		Domain master = DomainUtil.findDomainInListOrReturnNull(domains, masterName);
		if (master == null) {
			info.addCompileError("域对象"+masterName +"未找到!");
		} else {
			mtmc.setMaster(master);
			mtmc.setMasterName(masterName);
		}
		
		Domain slave = DomainUtil.findDomainInListOrReturnNull(domains, slaveName);
		if (slave == null) {
			info.addCompileError("域对象"+slaveName +"未找到!");
		}else {
			mtmc.setSlave(slave);
		}
		mtmc.setSlaveAlias(slaveAlias);
		mtmc.setTablePrefix(tableprefix);
		mtmc.setLabel(label);
		
		Field masterId = new Field();
		masterId.setFieldType("i64");
		masterId.setFieldName(masterIdName);
		mtmc.setMasterId(masterId);
		
		Field slaveId = new Field();
		slaveId.setFieldType("i64");
		slaveId.setFieldName(slaveIdName);
		mtmc.setSlaveId(slaveId);
		
		mtmc.setLabel(label);
		mtmc.setTablePrefix(tableprefix);		
		mtmc.setSlaveAlias(slaveAlias);
			
		if (!info.success(true)) throw new ValidateException(info);		
		return mtmc;
	}
	
	public List<ManyToManyCandidate> translateManyToManyCandidateValues(Project project,HSSFSheet sheet,List<Domain> domains,List<List<Domain>> datadomains)  throws Exception{
		ManyToManyCandidate mtmc = translateManyToManyCandidate(project,sheet,domains,datadomains);
		
		Cell metaCell = locateKeyCell(sheet, "元字段类型");
		Cell dataCell = locateKeyCell(sheet, "数据");
		List<ManyToManyCandidate> vmtmcs = new ArrayList<>();
		for (int i = dataCell.getRowIndex(); i < findOutLastDataRowIndex(sheet, findOutIdColIndex(sheet),
				metaCell.getRowIndex()); i++) {		
			ManyToManyCandidate vmtmc = (ManyToManyCandidate)mtmc.deepClone();
			String masterIdValue = readMtmCandidateMasterIdFieldValue(sheet, findOutIdColIndex(sheet),
					dataCell.getRowIndex(),metaCell.getRowIndex(),i);
			String slaveIdsValue = readMtmCandidateSlaveIdsFieldValue(sheet, findOutIdColIndex(sheet),
					dataCell.getRowIndex(),metaCell.getRowIndex(),i);
			vmtmc.setMasterIdValue(masterIdValue);
			vmtmc.setSlaveIdValues(slaveIdsValue);	
			vmtmcs.add(vmtmc);
		}
		return vmtmcs;
	}
		
	public int findOutIdColIndex(HSSFSheet sheet) {
		Cell metaFieldCell = locateKeyCell(sheet, "元字段类型");
		for (int i = metaFieldCell.getColumnIndex() + 1; i < sheet.getRow(metaFieldCell.getRowIndex())
				.getLastCellNum(); i++) {
			if (sheet.getRow(metaFieldCell.getRowIndex()).getCell(i).getStringCellValue().equals("id")) {
				return i;
			}
		}
		return metaFieldCell.getColumnIndex() + 1;
	}
	
	public String readFieldMeta(HSSFSheet sheet, int columIndex, int metaFieldIndex, String dbType) {
		String metafield = getCellStringValue(sheet.getRow(metaFieldIndex).getCell(columIndex));
		return metafield;
	}
	
	public int findOutLastDataRowIndex(HSSFSheet sheet, int idColIndex, int beginRowIndex) {
		for (int i = beginRowIndex; i <= sheet.getLastRowNum(); i++) {
			if (sheet.getRow(i)==null ||sheet.getRow(i).getCell(idColIndex)==null || StringUtil.isBlank(getCellStringValue(sheet.getRow(i).getCell(idColIndex))))
				return i;
		}
		return sheet.getLastRowNum()+1;
	}	

	public String getCellStringValue(Cell c) {
		if (c==null) return "";
		if (c.getCellType() == CellType.STRING) {
			String retVal = c.getStringCellValue();
			if (!StringUtil.isBlank(retVal)&&retVal.charAt(0)=='\'') return retVal.substring(1,retVal.length());
			else if (StringUtil.isBlank(retVal)) return "";
			else return retVal;
		}
			
		else if (c.getCellType() == CellType.NUMERIC) {
			short format = c.getCellStyle().getDataFormat();
			if (format == 14 || format == 31 || format == 57 || format == 58) {
				DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
				Date date = DateUtil.getJavaDate(c.getNumericCellValue());
				String value = formater.format(date);
				return value;
			} else if (format == 20 || format == 32) {
				DateFormat formater = new SimpleDateFormat("HH:mm");
				Date date = DateUtil.getJavaDate(c.getNumericCellValue());
				String value = formater.format(date);
				return value;
			}
			double dis = c.getNumericCellValue() - Math.round(c.getNumericCellValue());
			if (dis > 0.0001d) {
				return "" + c.getNumericCellValue();
			} else {
				return "" + (long) Math.round(c.getNumericCellValue());
			}
		} 
			return "";
	}
	
	public String readMetaField(HSSFSheet metaSheet, String key) {
		Cell c = locateKeyCell(metaSheet, key);
		if (c == null)
			return "";
		else
			// return
			// metaSheet.getRow(c.getRowIndex()).getCell(c.getColumnIndex() +
			// 1).getStringCellValue();
			return this.getCellStringValue(metaSheet.getRow(c.getRowIndex()).getCell(c.getColumnIndex() + 1)).trim();
	}

	public Cell locateKeyCell(HSSFSheet metaSheet, String key) {
		int rowbegin = metaSheet.getFirstRowNum();
		int rowend = metaSheet.getLastRowNum();
		for (int i = rowbegin; i <= rowend; i++) {
			Row r = metaSheet.getRow(i);
			if(r!=null) {
				for (int j = r.getFirstCellNum(); j <= r.getLastCellNum(); j++) {
					Cell c = r.getCell(j);
					if (c != null && this.getCellStringValue(c).equalsIgnoreCase(key))
						return c;
				}
			}
		}
		return null;
	}
	
	public String readMtmCandidateMasterIdFieldValue(HSSFSheet sheet, int beginColIndex, int metaFieldRowIndex,
			int fieldNameRowIndex, int rowIndex) throws ValidateException {
		for (int i = beginColIndex; i < sheet.getRow(fieldNameRowIndex).getLastCellNum(); i++) {
			Cell c = sheet.getRow(fieldNameRowIndex).getCell(i);
			String cellfieldName = c.getStringCellValue();
			if (cellfieldName.contains("_"))
				cellfieldName = StringUtil.changeTableColumtoDomainField(cellfieldName);
			if (!StringUtil.isBlank(cellfieldName) && cellfieldName.equals("masterId")) {
				return getCellStringValue(sheet.getRow(rowIndex).getCell(i));
			}
		}
		return "";
	}
	
	public String readMtmCandidateSlaveIdsFieldValue(HSSFSheet sheet, int beginColIndex, int metaFieldRowIndex,
			int fieldNameRowIndex, int rowIndex) throws ValidateException {
		for (int i = beginColIndex; i < sheet.getRow(fieldNameRowIndex).getLastCellNum(); i++) {
			Cell c = sheet.getRow(fieldNameRowIndex).getCell(i);
			String cellfieldName = c.getStringCellValue();
			if (cellfieldName.contains("_"))
				cellfieldName = StringUtil.changeTableColumtoDomainField(cellfieldName);
			if (!StringUtil.isBlank(cellfieldName) && cellfieldName.equals("slaveIds")) {
				return getCellStringValue(sheet.getRow(rowIndex).getCell(i));
			}
		}
		return "";
	}
	
	public static void main(String[] args) {		
	}
}
